package com.ysd.Dao.Impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.ysd.entity.Banner;
import com.ysd.entity.News;
import com.ysd.entity.Product;
import com.ysd.util.DBmanager;

/**
 * 通过系列id获取产品系列列表
 * @author 86176
 *
 */
public class ProductDaoImpl {
	public List<Product> getProductList(int psId){
		List<Product> list = new ArrayList<Product>();
		//1建立连接
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {//,try正常执行
			//2,创建statement执行对象
			st = conn.createStatement();
			//3,SQL语句
			String sql = "select * from product where productCategoryId="+psId;
			rs = st.executeQuery(sql);
			while(rs.next()) {
			    Integer	productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				Integer productPrice = rs.getInt("productPrice");
				Integer salePrice= rs.getInt("salePrice");
				String productTexture = rs.getString("productTexture");
				Integer productCategoryId= rs.getInt("productCategoryId");
				String productPicture= rs.getString("productPicture");
				String productMaterial = rs.getString("productMaterial");
				String productBrand = rs.getString("productBrand");
				String productSize = rs.getString("productSize");
				String productNetDegree = rs.getString("productNetDegree");
				String productColor = rs.getString("productColor");
				String isCutomize = rs.getString("isCutomize");
				String productSpecification = rs.getString("productSpecification");
				Product product=new Product(productId, productName, productPrice, salePrice, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification);
				list.add(product);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	
/**
 * 
 * 通过产品id获取指定id产品详细数据
 * @param pId
 * @return
 */
	public Product getProductListById(int pId){
		Product product = null;
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
		
			st = conn.createStatement();
			
			String sql = "select p.* ,ps.productSeriesName FROM product p,productseries ps where productId='"+pId+"' AND p.productCategoryId = ps.productSeriesId " ;
			rs = st.executeQuery(sql);
			while(rs.next()) {
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int salePrice = rs.getInt("salePrice");
				int productPrice =rs.getInt("productPrice");
				String productTexture = rs.getString("productTexture");
				int productCategoryId = rs.getInt("productCategoryId");
				String productPicture =rs.getString("productPicture");
				String productMaterial =rs.getString("productMaterial");
				String productBrand =rs.getString("productBrand");
				String productSize =rs.getString("productSize");
				String productNetDegree =rs.getString("productNetDegree");
				String productColor =rs.getString("productColor");
				String isCutomize =rs.getString("isCutomize");
				String productSpecification =rs.getString("productSpecification");
				String productSeriesName =rs.getString("productSeriesName");
				
				product = new Product(productId, productName, productPrice, salePrice, 0,0, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification, productSeriesName);
			}
			return product;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}

/**
 * 查询产品表所有数据
 * @return
 */
	public List<Product> getProductListAll( ){
		List<Product> list = new ArrayList<Product>();
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			String sql = "select * from product";
			rs = st.executeQuery(sql);
			while(rs.next()) {
			    Integer	productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				Integer productPrice = rs.getInt("productPrice");
				Integer salePrice= rs.getInt("salePrice");
				String productTexture = rs.getString("productTexture");
				Integer productCategoryId= rs.getInt("productCategoryId");
				String productPicture= rs.getString("productPicture");
				String productMaterial = rs.getString("productMaterial");
				String productBrand = rs.getString("productBrand");
				String productSize = rs.getString("productSize");
				String productNetDegree = rs.getString("productNetDegree");
				String productColor = rs.getString("productColor");
				String isCutomize = rs.getString("isCutomize");
				String productSpecification = rs.getString("productSpecification");
				Product product=new Product(productId, productName, productPrice, salePrice, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification);
				list.add(product);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	

	/**
	 * 查询产品表所有数据,随机图片实现类
	 * @return
	 */

	public List<Product> getProductranderList( ){
		List<Product> list = new ArrayList<Product>();
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		
		try {
			st = conn.createStatement();
			
			String sql = "select * from product order by rand() limit 6";
			rs = st.executeQuery(sql);
			while(rs.next()) {
			    Integer	productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				Integer productPrice = rs.getInt("productPrice");
				Integer salePrice= rs.getInt("salePrice");
				String productTexture = rs.getString("productTexture");
				Integer productCategoryId= rs.getInt("productCategoryId");
				String productPicture= rs.getString("productPicture");
				String productMaterial = rs.getString("productMaterial");
				String productBrand = rs.getString("productBrand");
				String productSize = rs.getString("productSize");
				String productNetDegree = rs.getString("productNetDegree");
				String productColor = rs.getString("productColor");
				String isCutomize = rs.getString("isCutomize");
				String productSpecification = rs.getString("productSpecification");
				Product product=new Product(productId, productName, productPrice, salePrice, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification);
				list.add(product);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 多条件分页查询产品详情
	 * @author 86176
	 *
	 */
	public List<Product> getProductsByWhere(Product p,int page,int size){
		List<Product> list = new ArrayList<Product>();
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			
			String sql = "select p.* ,ps.productSeriesName FROM product p,productseries ps where 1=1 ";//where是永真条件,永远为true,目的是解决拼接问题
			
			if(p.getProductName()!=null && !p.getProductName().equals("")) {
				sql+="  and productName like '%"+p.getProductName()+"%' ";
			}
			if(p.getBeginPrice() > 0) {
				sql+=" and salePrice > "+p.getBeginPrice();
			}
			if(p.getEndPrice() >0) {
				sql+=" and salePrice < "+p.getEndPrice();
			}
			if(p.getProductMaterial()!=null && !p.getProductMaterial().equals("")) {
				sql+=" and productMaterial like '%"+p.getProductMaterial()+"%' ";
			}
			if(p.getProductSize() !=null && !p.getProductSize().equals("")) {
				sql+=" and productSize like '%"+p.getProductSize()+"%'";
			}
			if(p.getProductNetDegree() !=null && !p.getProductNetDegree().equals("")) {
				sql+=" and productNetDegree ='"+p.getProductNetDegree()+"'";
			}
			if(p.getProductColor() !=null && !p.getProductColor().equals("")) {
				sql+=" and productColor like '%"+p.getProductColor()+"%' ";
			}
			if(p.getIsCutomize() !=null && !p.getIsCutomize().equals("")) {
				sql+=" and isCutomize like '%"+p.getIsCutomize()+"%' ";
			}
			if(p.getProductSpecification() !=null && !p.getProductSpecification().equals("")) {
				sql+=" and productSpecification like '%"+p.getProductSpecification()+"%' ";
			}
			if(p.getProductCategoryId() >0) {
				sql+=" and productCategoryId ="+p.getProductCategoryId();
			}
			sql+="  AND p.productCategoryId = ps.productSeriesId order by productId desc limit "+page+","+size;
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()){
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int salePrice = rs.getInt("salePrice");
				int productPrice =rs.getInt("productPrice");
				String productTexture = rs.getString("productTexture");
				int productCategoryId = rs.getInt("productCategoryId");
				String productPicture =rs.getString("productPicture");
				String productMaterial =rs.getString("productMaterial");
				String productBrand =rs.getString("productBrand");
				String productSize =rs.getString("productSize");
				String productNetDegree =rs.getString("productNetDegree");
				String productColor =rs.getString("productColor");
				String isCutomize =rs.getString("isCutomize");
				String productSpecification =rs.getString("productSpecification");
				String productSeriesName =rs.getString("productSeriesName");
				
				Product product = new Product(productId,productName, productPrice, salePrice, 0, 0, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification, productSeriesName);
				list.add(product);
			}
			return list;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		}finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	/**
	 * 获取集合长度
	 * @author 86176
	 *
	 */
	public List<Product> getProductsAll(Product p){
		List<Product> list = new ArrayList<Product>();
		Connection conn = DBmanager.getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			String sql = "select p.* ,ps.productSeriesName FROM product p,productseries ps where 1=1 ";//where是永真条件,永远为true,目的是解决拼接问题
			
			if(p.getProductName()!=null && !p.getProductName().equals("")) {
				sql+="  and productName like '%"+p.getProductName()+"%' ";
			}
			if(p.getBeginPrice() > 0) {
				sql+=" and salePrice > "+p.getBeginPrice();
			}
			if(p.getEndPrice() >0) {
				sql+=" and salePrice < "+p.getEndPrice();
			}
			if(p.getProductMaterial()!=null && !p.getProductMaterial().equals("")) {
				sql+=" and productMaterial like '%"+p.getProductMaterial()+"%' ";
			}
			if(p.getProductSize() !=null && !p.getProductSize().equals("")) {
				sql+=" and productSize like '%"+p.getProductSize()+"%'";
			}
			if(p.getProductNetDegree() !=null && !p.getProductNetDegree().equals("")) {
				sql+=" and productNetDegree ='"+p.getProductNetDegree()+"'";			
			}
			if(p.getProductColor() !=null && !p.getProductColor().equals("")) {
				sql+=" and productColor like '%"+p.getProductColor()+"%' ";
			}
			if(p.getIsCutomize() !=null && !p.getIsCutomize().equals("")) {
				sql+=" and isCutomize like '%"+p.getIsCutomize()+"%' ";
			}
			if(p.getProductSpecification() !=null && !p.getProductSpecification().equals("")) {
				sql+=" and productSpecification like '%"+p.getProductSpecification()+"%' ";
			}
			if(p.getProductCategoryId() >0) {
				sql+=" and productCategoryId ="+p.getProductCategoryId();
			}
			sql+="  AND p.productCategoryId = ps.productSeriesId order by productId desc ";
			System.out.println(sql);
			rs = st.executeQuery(sql);
			while(rs.next()){
				int productId = rs.getInt("productId");
				String productName = rs.getString("productName");
				int salePrice = rs.getInt("salePrice");
				int productPrice =rs.getInt("productPrice");
				String productTexture = rs.getString("productTexture");
				int productCategoryId = rs.getInt("productCategoryId");
				String productPicture =rs.getString("productPicture");
				String productMaterial =rs.getString("productMaterial");
				String productBrand =rs.getString("productBrand");
				String productSize =rs.getString("productSize");
				String productNetDegree =rs.getString("productNetDegree");
				String productColor =rs.getString("productColor");
				String isCutomize =rs.getString("isCutomize");
				String productSpecification =rs.getString("productSpecification");
				String productSeriesName =rs.getString("productSeriesName");
				
				Product product = new Product(productId,productName, productPrice, salePrice, 0, 0, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification, productSeriesName);
				list.add(product);
			}
			return list;
		} catch (SQLException e) {
			
			e.printStackTrace();
			return null;
		}finally {
			DBmanager.closeAll(conn, st, rs);
		}
	}
	
	/**
	 * 
	 * 根据产品id删除产品
	 * pId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteProductById(int pId) {
		return DBmanager.executeUpdate("DELETE FROM product where productId ="+pId);
	}
	
	/**
	 * 
	 * 根据产品id批量删除产品
	 * pId前台传来的参数
	 * @author 86176
	 *return 返回受影响行数
	 */
	public int deleteProductByIds(String pIds) {
		String sql ="DELETE FROM product where productId in("+pIds+")";
		System.out.println(sql);
		return DBmanager.executeUpdate(sql);
	}
	/**
	 *  根据产品名称查询该名称是否存在
	 * @return
	 */
		public Product getProductListByName(String name ){
			Product product=null;
			Connection conn = DBmanager.getConnection();
			Statement st = null;
			ResultSet rs = null;
			try {
				st = conn.createStatement();
				String sql = "select * from product where  productName='"+name+"' ";
				rs = st.executeQuery(sql);
				while(rs.next()) {
				    Integer	productId = rs.getInt("productId");
					String productName = rs.getString("productName");
					Integer productPrice = rs.getInt("productPrice");
					Integer salePrice= rs.getInt("salePrice");
					String productTexture = rs.getString("productTexture");
					Integer productCategoryId= rs.getInt("productCategoryId");
					String productPicture= rs.getString("productPicture");
					String productMaterial = rs.getString("productMaterial");
					String productBrand = rs.getString("productBrand");
					String productSize = rs.getString("productSize");
					String productNetDegree = rs.getString("productNetDegree");
					String productColor = rs.getString("productColor");
					String isCutomize = rs.getString("isCutomize");
					String productSpecification = rs.getString("productSpecification");
					 product=new Product(productId, productName, productPrice, salePrice, productTexture, productCategoryId, productPicture, productMaterial, productBrand, productSize, productNetDegree, productColor, isCutomize, productSpecification);
				}
				return product;
			} catch (SQLException e) {
				e.printStackTrace();
				return null;
			} finally {
				DBmanager.closeAll(conn, st, rs);
			}
		}
		/**
		   *添加产品
		 * p 前台传来的产品数据
		 *return 受影响行数
		 */
		public int addProduct(Product p) {
			String sql = "insert into product VALUES(null,'"+p.getProductName()+"','"+p.getProductPrice()+"','"+p.getSalePrice()+"','"+p.getProductTexture()+"','"+p.getProductCategoryId()+"','"+p.getProductPicture()+"','"+p.getProductMaterial()+"','"+p.getProductBrand()+"','"+p.getProductSize()+"','"+p.getProductNetDegree()+"','"+p.getProductColor()+"','"+p.getIsCutomize()+"','"+p.getProductSpecification()+"')";
			System.out.println(sql);
			return DBmanager.executeUpdate(sql);
			
		}
		
		/**
		   *编辑产品
		 * p 前台传来的产品数据
		 *return 受影响行数
		 */
		public int  editProduct(Product p) {
			String sql = "update product set productName = '"+p.getProductName()+"',productPrice='"+p.getProductPrice()+"',productPicture='"+p.getProductPicture()+"',salePrice='"+p.getSalePrice()+"',productTexture='"+p.getProductTexture()+"',productCategoryId='"+p.getProductCategoryId()+"',productBrand='"+p.getProductBrand()+"',productSize='"+p.getProductSize()+"',productColor='"+p.getProductColor()+"',productNetDegree='"+p.getProductNetDegree()+"',productSpecification='"+p.getProductSpecification()+"',isCutomize='"+p.getIsCutomize()+"',productMaterial='"+p.getProductMaterial()+"' where productId = '"+p.getProductId()+"' ";
			System.out.println(sql);
			return DBmanager.executeUpdate(sql);
			
			
		}
}
